{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "{\n",
    " \"cells\": [\n",
    "  {\n",
    "   \"cell_type\": \"markdown\",\n",
    "   \"metadata\": {},\n",
    "   \"source\": [\n",
    "    \"# Speedtest数据分析\\n\",\n",
    "    \"本notebook用于分析Speedtest测速数据，包含数据预处理、数据汇聚和相关性分析。\"\n",
    "   ]\n",
    "  },\n",
    "  {\n",
    "   \"cell_type\": \"code\",\n",
    "   \"execution_count\": null,\n",
    "   \"metadata\": {},\n",
    "   \"source\": [\n",
    "    \"import pandas as pd\\n\",\n",
    "    \"import numpy as np\\n\",\n",
    "    \"import matplotlib.pyplot as plt\\n\",\n",
    "    \"import seaborn as sns\\n\",\n",
    "    \"from scipy import stats\\n\",\n",
    "    \"\\n\",\n",
    "    \"# 设置显示格式\\n\",\n",
    "    \"pd.set_option('display.max_rows', 100)\\n\",\n",
    "    \"pd.set_option('display.max_columns', 100)\\n\",\n",
    "    \"plt.style.use('seaborn')\"\n",
    "   ]\n",
    "  },\n",
    "  {\n",
    "   \"cell_type\": \"markdown\",\n",
    "   \"metadata\": {},\n",
    "   \"source\": [\n",
    "    \"## 1. 数据预处理\"\n",
    "   ]\n",
    "  },\n",
    "  {\n",
    "   \"cell_type\": \"code\",\n",
    "   \"execution_count\": null,\n",
    "   \"metadata\": {},\n",
    "   \"source\": [\n",
    "    \"# 读取CSV文件\\n\",\n",
    "    \"df = pd.read_csv('your_speedtest_data.csv')\\n\",\n",
    "    \"\\n\",\n",
    "    \"# 1.1 筛选Dubai地区的数据\\n\",\n",
    "    \"df = df[df['attr_location'] == 'Asia/Dubai']\\n\",\n",
    "    \"\\n\",\n",
    "    \"# 1.2 删除错行数据（这里假设关键字段都不应该为空）\\n\",\n",
    "    \"key_columns = ['attr_location_latitude', 'attr_location_longitude',\\n\",\n",
    "    \"              'attr_connection_downstream_bandwidth_kbps',\\n\",\n",
    "    \"              'attr_connection_upstream_bandwidth_kbps',\\n\",\n",
    "    \"              'num_packet_loss_received',\\n\",\n",
    "    \"              'id_device']\\n\",\n",
    "    \"df = df.dropna(subset=key_columns)\\n\",\n",
    "    \"\\n\",\n",
    "    \"# 1.3 处理经纬度精度和速率单位\\n\",\n",
    "    \"df['attr_location_latitude'] = df['attr_location_latitude'].round(3)\\n\",\n",
    "    \"df['attr_location_longitude'] = df['attr_location_longitude'].round(3)\\n\",\n",
    "    \"df['downstream_mbps'] = df['attr_connection_downstream_bandwidth_kbps'] / 1000\\n\",\n",
    "    \"df['upstream_mbps'] = df['attr_connection_upstream_bandwidth_kbps'] / 1000\\n\",\n",
    "    \"\\n\",\n",
    "    \"print(f\\\"预处理后的数据条数：{len(df)}\\\")\"\n",
    "   ]\n",
    "  },\n",
    "  {\n",
    "   \"cell_type\": \"markdown\",\n",
    "   \"metadata\": {},\n",
    "   \"source\": [\n",
    "    \"## 2. 数据汇聚\"\n",
    "   ]\n",
    "  },\n",
    "  {\n",
    "   \"cell_type\": \"code\",\n",
    "   \"execution_count\": null,\n",
    "   \"metadata\": {},\n",
    "   \"source\": [\n",
    "    \"# 按经纬度分组并计算统计量\\n\",\n",
    "    \"grouped_df = df.groupby(['attr_location_latitude', 'attr_location_longitude']).agg({\\n\",\n",
    "    \"    'id_device': 'count',\\n\",\n",
    "    \"    'downstream_mbps': 'median',\\n\",\n",
    "    \"    'upstream_mbps': 'median',\\n\",\n",
    "    \"    'num_packet_loss_received': 'median'\\n\",\n",
    "    \"}).reset_index()\\n\",\n",
    "    \"\\n\",\n",
    "    \"# 重命名列\\n\",\n",
    "    \"grouped_df.columns = ['latitude', 'longitude', 'id_device_count', \\n\",\n",
    "    \"                     'median_DL_mbps', 'median_UL_mbps', 'median_packet_loss']\\n\",\n",
    "    \"\\n\",\n",
    "    \"print(\\\"汇聚后的数据示例：\\\")\\n\",\n",
    "    \"display(grouped_df.head())\"\n",
    "   ]\n",
    "  },\n",
    "  {\n",
    "   \"cell_type\": \"markdown\",\n",
    "   \"metadata\": {},\n",
    "   \"source\": [\n",
    "    \"## 3. 相关性分析\"\n",
    "   ]\n",
    "  },\n",
    "  {\n",
    "   \"cell_type\": \"code\",\n",
    "   \"execution_count\": null,\n",
    "   \"metadata\": {},\n",
    "   \"source\": [\n",
    "    \"def calculate_correlation(data):\\n\",\n",
    "    \"    \\\"\\\"\\\"计算相关系数\\\"\\\"\\\"\\n\",\n",
    "    \"    corr_dl = stats.pearsonr(data['id_device_count'], data['median_DL_mbps'])[0]\\n\",\n",
    "    \"    corr_ul = stats.pearsonr(data['id_device_count'], data['median_UL_mbps'])[0]\\n\",\n",
    "    \"    corr_pl = stats.pearsonr(data['id_device_count'], data['median_packet_loss'])[0]\\n\",\n",
    "    \"    return pd.Series({'corr_DL': corr_dl, 'corr_UL': corr_ul, 'corr_packet_loss': corr_pl})\\n\",\n",
    "    \"\\n\",\n",
    "    \"def create_analysis_table(data, sort_column, ascending=False, n=100):\\n\",\n",
    "    \"    \\\"\\\"\\\"创建分析表格\\\"\\\"\\\"\\n\",\n",
    "    \"    result = data.sort_values(sort_column, ascending=ascending).head(n)\\n\",\n",
    "    \"    correlations = calculate_correlation(result)\\n\",\n",
    "    \"    print(f\\\"\\\\n相关系数：\\\\n{correlations}\\\\n\\\")\\n\",\n",
    "    \"    return result\"\n",
    "   ]\n",
    "  },\n",
    "  {\n",
    "   \"cell_type\": \"markdown\",\n",
    "   \"metadata\": {},\n",
    "   \"source\": [\n",
    "    \"### 4.1 设备数量Top 100分析\"\n",
    "   ]\n",
    "  },\n",
    "  {\n",
    "   \"cell_type\": \"code\",\n",
    "   \"execution_count\": null,\n",
    "   \"metadata\": {},\n",
    "   \"source\": [\n",
    "    \"top_devices = create_analysis_table(grouped_df, 'id_device_count', ascending=False)\\n\",\n",
    "    \"display(top_devices[['id_device_count', 'median_UL_mbps', 'median_DL_mbps', 'median_packet_loss']])\"\n",
    "   ]\n",
    "  },\n",
    "  {\n",
    "   \"cell_type\": \"markdown\",\n",
    "   \"metadata\": {},\n",
    "   \"source\": [\n",
    "    \"### 4.2 下行速率最低100条分析\"\n",
    "   ]\n",
    "  },\n",
    "  {\n",
    "   \"cell_type\": \"code\",\n",
    "   \"execution_count\": null,\n",
    "   \"metadata\": {},\n",
    "   \"source\": [\n",
    "    \"lowest_dl = create_analysis_table(grouped_df, 'median_DL_mbps', ascending=True)\\n\",\n",
    "    \"display(lowest_dl[['id_device_count', 'median_UL_mbps', 'median_DL_mbps', 'median_packet_loss']])\"\n",
    "   ]\n",
    "  },\n",
    "  {\n",
    "   \"cell_type\": \"markdown\",\n",
    "   \"metadata\": {},\n",
    "   \"source\": [\n",
    "    \"### 4.3 上行速率最低100条分析\"\n",
    "   ]\n",
    "  },\n",
    "  {\n",
    "   \"cell_type\": \"code\",\n",
    "   \"execution_count\": null,\n",
    "   \"metadata\": {},\n",
    "   \"source\": [\n",
    "    \"lowest_ul = create_analysis_table(grouped_df, 'median_UL_mbps', ascending=True)\\n\",\n",
    "    \"display(lowest_ul[['id_device_count', 'median_UL_mbps', 'median_DL_mbps', 'median_packet_loss']])\"\n",
    "   ]\n",
    "  },\n",
    "  {\n",
    "   \"cell_type\": \"markdown\",\n",
    "   \"metadata\": {},\n",
    "   \"source\": [\n",
    "    \"### 4.4 丢包率最高100条分析\"\n",
    "   ]\n",
    "  },\n",
    "  {\n",
    "   \"cell_type\": \"code\",\n",
    "   \"execution_count\": null,\n",
    "   \"metadata\": {},\n",
    "   \"source\": [\n",
    "    \"highest_packet_loss = create_analysis_table(grouped_df, 'median_packet_loss', ascending=False)\\n\",\n",
    "    \"display(highest_packet_loss[['id_device_count', 'median_packet_loss', 'median_UL_mbps', 'median_DL_mbps']])\"\n",
    "   ]\n",
    "  },\n",
    "  {\n",
    "   \"cell_type\": \"markdown\",\n",
    "   \"metadata\": {},\n",
    "   \"source\": [\n",
    "    \"## 5. 可视化分析\"\n",
    "   ]\n",
    "  },\n",
    "  {\n",
    "   \"cell_type\": \"code\",\n",
    "   \"execution_count\": null,\n",
    "   \"metadata\": {},\n",
    "   \"source\": [\n",
    "    \"def plot_correlation(data, x_col, y_col, title):\\n\",\n",
    "    \"    plt.figure(figsize=(10, 6))\\n\",\n",
    "    \"    sns.scatterplot(data=data, x=x_col, y=y_col)\\n\",\n",
    "    \"    plt.title(title)\\n\",\n",
    "    \"    plt.xlabel(x_col)\\n\",\n",
    "    \"    plt.ylabel(y_col)\\n\",\n",
    "    \"    plt.show()\\n\",\n",
    "    \"\\n\",\n",
    "    \"# 绘制相关性散点图\\n\",\n",
    "    \"plot_correlation(grouped_df, 'id_device_count', 'median_DL_mbps', '设备数量与下行速率关系')\\n\",\n",
    "    \"plot_correlation(grouped_df, 'id_device_count', 'median_UL_mbps', '设备数量与上行速率关系')\\n\",\n",
    "    \"plot_correlation(grouped_df, 'id_device_count', 'median_packet_loss', '设备数量与丢包率关系')\"\n",
    "   ]\n",
    "  }\n",
    " ],\n",
    " \"metadata\": {\n",
    "  \"kernelspec\": {\n",
    "   \"display_name\": \"Python 3\",\n",
    "   \"language\": \"python\",\n",
    "   \"name\": \"python3\"\n",
    "  }\n",
    " }\n",
    "}"
   ]
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
